package com.emar.spring.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.emar.spring.common.util.StringUtil;
import com.emar.spring.dto.ParamsDTO;
import com.emar.spring.model.SystemEntity;

@Repository
public class SystemDao extends BaseDAO {

	private String getListSql(ParamsDTO dto, String groupBy, int pageNumber,
			int pageSize) {
		StringBuffer sb = new StringBuffer(
				" SELECT s.`SystemId`, s.`SystemName`, s.`SystemDescribe`, s.`ProtectSortCode`, s.`SecurityLevelCode`, p.`ProjectID`, p.`ProjectName`, p.`ProjectType`, p.`ProjectCode`, p.`ProjectStatus`, p.`ProjectDescribe`, p.`ProjectManagerID`, p.`CommissionCorpName`, p.`CommissionDate`, p.`CommissionCorpLinkName`, p.`CommissionCorpLinkTel`, p.`TargetSystemName` FROM t_system AS s LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` ");
		sb.append(this.getWhereSql(dto));
		if (StringUtil.isNotEmpty(groupBy)) {
			sb.append(" GROUP BY (" + groupBy + ")");
		}
		return sb.toString();
	}

	private String getCount(ParamsDTO dto, String groupBy, int pageNumber,
			int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT ");
		sb.append(" COUNT(*) ");
		sb.append(" FROM ( ");
		sb.append(this.getListSql(dto, groupBy, pageNumber, pageSize));
		sb.append(" ) as t");
		return sb.toString();
	}
	
	public int getSystemCount(ParamsDTO dto, String groupBy, int pageNumber,
			int pageSize) {
		String sql = this.getCount(dto, groupBy, pageNumber, pageSize);
		return super.getCount(sql);
	}

	public List<ParamsDTO> getParamsDTOPageList(ParamsDTO pe, String groupBy,
			int pageNumber, int pageSize) {
		String sql = this.getListSql(pe, groupBy, pageNumber, pageSize);
		List<ParamsDTO> list = this.getPageList(sql, ParamsDTO.class,
				pageNumber, pageSize);
		return list;
	}
	
	public List<ParamsDTO> getSingleSystemSecuList(ParamsDTO pe ,
			int pageNumber, int pageSize) {
		StringBuffer sb = new StringBuffer(" SELECT COUNT(*) AS COUNT, r.`RecordId`, r.`ObjName`, r.`ObjType1`, r.`ObjType2`, r.`isValid`, r.`RecordName`, r.`RecordJudge`, s.`SystemId`, s.`SystemName`, s.`SystemDescribe`, s.`ProtectSortCode`, s.`SecurityLevelCode`, p.`ProjectID`, p.`ProjectName`, p.`ProjectType`, p.`ProjectCode`, p.`ProjectStatus`, p.`ProjectDescribe`, p.`ProjectManagerID`, p.`CommissionCorpName`, p.`CommissionDate`, p.`CommissionCorpLinkName`, p.`CommissionCorpLinkTel`, p.`TargetSystemName`, t.`TestItemCode`, t.`TestItemDescribe`, t.`ProtectSortCode` AS tProtectSortCode, t.`SecurityLevelCode` AS tSecurityLevelCode FROM t_record AS r LEFT JOIN t_system AS s ON r.`SystemId` = s.`SystemId` LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` LEFT JOIN t_test AS t ON r.`TestItemCode` = t.`TestItemCode`");
		sb.append( " WHERE s.`SystemId` = '"+pe.getSystemId()+"'");
		sb.append( " GROUP BY "+pe.getGroupBy());
		List<ParamsDTO> list = this.getList(sb.toString(), ParamsDTO.class);
		return list;
	}
	
	public List<ParamsDTO> getAllSystemSecuList(ParamsDTO pe, String groupBy,
			int pageNumber, int pageSize) {
		StringBuffer sb = new StringBuffer(
				" SELECT count(*) as count,s.`SystemId`, s.`SystemName`, s.`SystemDescribe`, s.`ProtectSortCode`, s.`SecurityLevelCode`, p.`ProjectID`, p.`ProjectName`, p.`ProjectType`, p.`ProjectCode`, p.`ProjectStatus`, p.`ProjectDescribe`, p.`ProjectManagerID`, p.`CommissionCorpName`, p.`CommissionDate`, p.`CommissionCorpLinkName`, p.`CommissionCorpLinkTel`, p.`TargetSystemName` FROM t_system AS s LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` ");
		sb.append(this.getWhereSql(pe));
		if (StringUtil.isNotEmpty(groupBy)) {
			sb.append(" GROUP BY (" + groupBy + ")");
		}
		List<ParamsDTO> list = this.getList(sb.toString(), ParamsDTO.class);
		return list;
	}
}
